Datasource creation in ColdFusion
To run a query in ColdFusion you have to define a datasource. The datasource is an object that contains connection and database properties. These are the properties which enable the query to execute on a specific local or remote database.

ColdFusion offers you 2 main ways to create a datasource:

1) via the ColdFusion Administrator;
2) via the THIS.DATASOURCES instance variable in Application.cfc .

The most common way is to define the datasource in the Datasource page of the ColdFusion Administrator. There you are provided with form fields to fill. (See pictures below.) This is an indispensable ColdFusion feature which has been there since the early ColdFusion versions.

ColdFusion 11 introduced the second way to define a datasource, namely, via THIS.DATASOURCES in Application.cfc. The datasources defined using THIS.DATASOURCES are specific to the application, unlike the datasources created in the Administrator, which are available to every application server-wide.

An example to show the THIS.DATASOURCES syntax:

this.datasources := {
dsn1 : { driver: “MSSQLServer”,
urlmap: {database: “testMSSQL”, host: “localhostMSSQL2008”},
username: “sa”,
password: “xxxxxxxx” },

dsn2: { username: “root”,
password: “xxxxxxxx”,
driver: “MySQL5”,
class: “com.mysql.jdbc.Driver”,
name:”bkbk_cf_mysql_db”,
url: “jdbc:mysql://127.0.0.1:3306/bkbk_cf_mysql_db?serverTimezone: Europe/London” },

dsn3:: { username: “sa”,
password: “xxxxxxx”
driver: “MSSQLServer”,
url:: “jdbc:macromedia:sqlserver://localhostMSSQL2008”,
urlmap: {database: “myMSSQL”, sendStringParametersAsUnicode: false, maxPooledStatements: 1000},
timeout: 0}
};

Hence THIS.DATASOURCES is a struct of structs. Its keys are the names of the datasources created for the application.

You will have noticed that this method requires you to know beforehand which properties to use in defining each datasource. This is in contrast to the ColdFusion Administrator method, where ColdFusion provides the required properties as form fields.

The example shows you some crucial keys:

Class
Driver
Name
Password
Username
Timeout
Url
UrlMap

So, how do know the names of the rest of the keys? How could you have guessed that a key, such as UrlMap, is itself a struct? What is its full set of keys?

The answers follow.

The complete list of keys of any arbitrary datasource, This.datasources[“myDSN”], is:

Alter
Blob_Buffer
Buffer
Class
Clientinfo
Create
Delete
Description
Disable
Disable_Autogenkeys
Disable_Blob
Disable_Clob
Driver
Drop
Grant
Insert
Interval
Isj2ee
Login_Timeout
Name
Password
Pooling
Revoke
Select
Storedproc
Timeout
Update
Url
Urlmap
Username
Validateconnection
Validationquery

To obtain the full description of the keys (structure, datatype, default value), run the following code as a CFM page:

<!— Login into Coldfusion Administrator. —>
<cfset createObject(“component”,”cfide.adminapi.administrator”).login(“your_cf_admin_password”)>

<!— Instantiate the data source object. —>
<cfset datasourceObject = createObject(“component”,”cfide.adminapi.datasource”)>

<!— Get a structure containing all the data sources —>
<cfset datasources = datasourceObject.getDatasources()>
<!— <cfdump var=”#datasources#” label=”All available data sources”> —>

<!— Get a structure containing the details of the cfArtGallery datasource —>
<cfset cfArtGalleryDatasource = datasources.cfartgallery>
<cfdump var=”#cfArtGalleryDatasource#” label=”cfArtGallery Datasource”>

The result is:

cfArtGallery Datasource – struct
CLASS	org.apache.derby.jdbc.EmbeddedDriver
DRIVER	Apache Derby Embedded
ISJ2EE	NO
NAME	cfartgallery
alter	YES
blob_buffer	64000
buffer	64000
clientinfo	cfArtGallery Datasource – struct ApplicationName NO ApplicationNamePrefix [empty string] ClientHostName NO ClientUser NO
create	YES
delete	YES
description	[empty string]
disable	NO
disable_autogenkeys	NO
disable_blob	YES
disable_clob	YES
drop	YES
grant	YES
insert	YES
interval	420
login_timeout	30
password	[empty string]
pooling	YES
revoke	YES
select	YES
storedproc	YES
timeout	1200
update	YES
url	jdbcerby:C:ColdFusion2021cfusiondbartgallery;create=false
urlmap	cfArtGallery Datasource – struct CONNECTIONPROPS cfArtGallery Datasource – struct DATABASE C:ColdFusion2021cfusiondbartgallery HOST [empty string] PORT 0 QTIMEOUT 0 SELECTMETHOD direct SENDSTRINGPARAMETERSASUNICODE false MaxPooledStatements 100 SID [empty string] ServiceName [empty string] TimeStampAsString NO UseTrustedConnection NO _logintimeout 30 applicationintent [empty string] args [empty string] coldiscoverysize 1000 database C:ColdFusion2021cfusiondbartgallery databaseFile [empty string] datasource [empty string] defaultpassword [empty string] defaultusername [empty string] host [empty string] informixServer [empty string] isnewdb false maxBufferSize [empty string] pageTimeout [empty string] port [empty string] qTimeout 0 selectMethod direct sendStringParametersAsUnicode false spylogfile [empty string] systemDatabaseFile [empty string] uppercaseid true usespylog NO writable false
username	[empty string]
validateConnection	NO
validationQuery	[empty string]